if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PersonnelXref_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PersonnelXref_Insert]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_PersonnelXref_Insert
(
@UserName	varchar(20) = NULL, 
@FirstName varchar(50), 
@LastName varchar(50),
@EmailAddress varchar(80) = NULL,
@Org 	varchar(32)
)
AS

IF @UserName IS NULL
	RETURN

SET @UserName = UPPER(@UserName)

DECLARE @_errorCode	AS INT

BEGIN TRANSACTION

IF NOT EXISTS(select 1 from DepartmentXref WHERE  DeptCode = @Org)
   BEGIN 
	SET @Org = '0100'
   END 

INSERT PersonnelXref (PersonID, LoginID, f_stat, fname, lname, email, DeptCode, inv, Login)
VALUES (@UserName, @UserName, 1, @FirstName, @LastName , @EmailAddress, @Org, 1, 1)

        SET @_errorCode = @@ERROR
        
        IF @_errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Unable to create PersonnelXref record for user %s', 16, 1, @userName)
            RETURN @_errorCode
        END

INSERT PersonnelPositionXref (PersonID, PositionCode)
VALUES (@UserName,'IN')

        SET @_errorCode = @@ERROR
        
        IF @_errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Unable to create PersonnelPositionXref record for user %s', 16, 1, @userName)
            RETURN @_errorCode
        END

INSERT INTO PersonnelDeptXref 
	(PersonID, DeptCode, PrimaryDept)
VALUES (@UserName, @Org, 1)

        SET @_errorCode = @@ERROR
        
        IF @_errorCode <> 0 BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('Unable to create PersonnelDeptXref record for user %s', 16, 1, @userName)
            RETURN @_errorCode
        END

COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

